Skip Headers

Oracle® Database Application Developer's Guide - Rules Manager and Expression Filter 10g Release 2 (10.2) Part Number B14288-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# B Application Examples

This appendix describes examples of applications using the Expression Filter.

Active Application

In an active database system, the server performs some actions when certain criteria are met. For example, an application can monitor changes to data in a database table and react to these changes accordingly.

Consider the `Car4Sale` application described in Chapter 1. In this application, the `Consumer` table stores the information about consumers interested in buying used cars. In addition to the `Consumer` table described in Chapter 1, assume that there is an `Inventory` table that stores information about all the used cars available for sale, as defined in the following example:

    CREATE TABLE Inventory (Model   VARCHAR2(20),
                            Year    NUMBER,
                            Price   NUMBER,
                            Mileage NUMBER);

Now, you can design the application such that the system reacts to any changes made to the data in the `Inventory` table, by defining a row trigger on the table:

    CREATE TRIGGER activechk AFTER insert OR update ON Inventory
      FOR EACH ROW
      DECLARE
        cursor c1 (ditem VARCHAR2) is
          SELECT CId, Phone FROM Consumer WHERE EVALUATE (Interest, ditem) = 1;
        ditem VARCHAR2(200);
     BEGIN
      ditem := Car4Sale.getVarchar(:new.Model, :new.Year, :new.Price, :new.Mileage);

      for cur in c1(ditem) loop
        DBMS_OUTPUT.PUT_LINE('  For Model '||:new.Model||' Call '||cur.CId||
                             ' @ '||cur.Phone);
      end loop;
    END;
    /

This trigger evaluates the expressions for every row inserted (or updated) into the `Inventory` table and prints a message if a consumer is interested in the car. An Expression Filter index on the `Interest` column can speed up the query on the `Consumer` table.

Batch Evaluation of Expressions

To evaluate a set of expressions for a batch of data items, you can perform a simple join of the table storing data items and the table storing expressions. You can join the `Consumer` table with the `Inventory` table to determine the interest in each car, as shown in the following example:

    SELECT DISTINCT Inventory.Model, count(*) as Demand
       FROM  Consumer, Inventory
       WHERE EVALUATE (Consumer.Interest,
                    Car4Sale.getVarchar(Inventory.Model,
                                        Inventory.Year,
                                        Inventory.Price,
                                        Inventory.Mileage)) = 1
       GROUP BY Inventory.Model
       ORDER BY Demand DESC;

The `EVALUATE` operator's join semantics can also be used to maintain complex N-to-M (many-to-many) relationships between data stored in multiple tables.

Resource Management

Consider an application that manages IT support resources based on the responsibilities (or duties) and the workload of each representative. In this application, the responsibilities of the representatives are captured as expressions defined using variables such as the priority of the problem, organization, and the environment.

Create a table named `ITResource` to store information about all the available representatives, as shown in the following example:

    -- Create the object type and the attribute set for ticket description --
    CREATE OR REPLACE TYPE ITTicket AS OBJECT (
                           Priority       NUMBER,
                           Environment    VARCHAR2(10),
                           Organization   VARCHAR2(10));
    /
    BEGIN
      DBMS_EXPFIL.CREATE_ATTRIBUTE_SET(attr_set => 'ITTicket',
                                       from_type => 'Yes');
    END;
    /

    -- Table storing expressions --
    CREATE TABLE ITResource (RId        NUMBER,
                             Duties     VARCHAR2(100));

    BEGIN
      DBMS_EXPFIL.ASSIGN_ATTRIBUTE_SET(attr_set => 'ITTicket',
                                       expr_tab => 'ITResource',
                                       expr_col => 'Duties');
    END;
    /

    INSERT INTO ITResource (RId, Duties) VALUES
       (1, 'Priority <= 2 and Environment = ''NT'' and Organization =
                                                       ''Research''');

    INSERT INTO ITResource (RId, Duties) VALUES
       (2, 'Priority = 1 and (Environment = ''UNIX'' or Environment = ''LINUX'')
            and Organization = ''APPS''');

Create a table named `ITProblem` to store the problems filed, as shown in the following example:

    CREATE TABLE ITProblem (PId             NUMBER,
                            Description     ITTicket,
                            AssignedTo      NUMBER);

The `AssignedTo` column in the `ITProblem` table stores the identifier of the representative handling the problem.

Now, use the following `UPDATE` statement to assign all the previously unassigned problems to capable IT representatives:

    UPDATE ITProblem p SET AssignedTo =
                 (SELECT RId FROM ITResource r
                  WHERE EVALUATE(r.Duties, p.Description.getVarchar()) = 1
                        and rownum < 2)
        WHERE AssignedTo IS NULL;

The previous `UPDATE` operation can benefit from an Expression Filter index defined on the `Duties` column of the `Resource` table.